﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Web;
using System.Web.Mvc;
using DataLook.App_Start;
using DataLook.Models;
using Newtonsoft.Json;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using Web;
using static DataLook.Controllers.ResultModel;

namespace DataLook.Controllers
{
	public class milesController : Controller
	{
		// GET: miles
		public ActionResult Index()
		{
			return View();
		}



		/// <summary>
		/// 导入卡用户
		/// </summary>
		/// <returns></returns>
		public JsonResult ImportCardUser()
		{

			//类别
			string category= Request["category"];
			var res = new JsonResult();
			
			ResultModel Result = new ResultModel();
			try
			{
				List<suMaiTong> suMaiTongList = new List<suMaiTong>();
				Dictionary<int, string> hastable = new UpLoadFileHelper().UploadExcelFile();
				if (hastable.FirstOrDefault().Key == 3)
				{
					string Excepath = hastable.FirstOrDefault().Value;
					DataTable table = NPOIEXcelHelper.ExcelToDataTable(Excepath, true);
					if (table == null)
					{
						Result = new ResultModel() { Code = 0, Message = "模板文件选择错误！" };
						//return Result;
					}
					suMaiTong smaitong = new suMaiTong();
                    MilesMode model = new MilesMode();
					SpecificationsInfo sModel = new SpecificationsInfo();
					List<ProductInfo> Pmodel1 = new List<ProductInfo>();
                    string Pmodel = null;

                    List<SpecificationsInfo> listGuiGe = new List<SpecificationsInfo>();
                    string listGuiGe1 = null;

                    for (int i = 0; i < table.Rows.Count; i++)
					{   //判断没有价格区间
                        if (!table.Rows[i][4].ToString().Contains("-"))
                        {
                            smaitong = new suMaiTong();
                            string guige = table.Rows[i][10].ToString(); //规格详情

                            if (!string.IsNullOrWhiteSpace(guige))
                            {
                                if (!guige.Contains("无规格信息"))
                                {
                                    listGuiGe = JsonConvert.DeserializeObject<List<SpecificationsInfo>>(guige);//解析 
                                    foreach (var item in listGuiGe)
                                    {
                                        listGuiGe1 += item.Color + ":" + item.sku + "。/n               ";
                                    }
                                }
                                else {
                                    listGuiGe1 = "";
                                }
                            }
                            smaitong.specificationsInfo = listGuiGe1;

                            smaitong.ProductID = table.Rows[i][2].ToString(); //唯一ID
                            smaitong.ProductName = table.Rows[i][3].ToString(); //商品名称 
                            smaitong.Category = category;//类别
                            smaitong.Price = table.Rows[i][4].ToString(); //价格

                            string productInfo = table.Rows[i][11].ToString(); //规格详情
                             
                            smaitong.productInfo = Pmodel;
                            string pictInFo = table.Rows[i][12].ToString(); //图片详情

                            string productImg = "";
                            //拆分图片
                            if (!string.IsNullOrWhiteSpace(pictInFo))
                            {
                                pictInFo = pictInFo.Replace("\"", "").Replace("]", "").Replace("[", "");
                                string[] picArr = pictInFo.Split(',');
                                if (picArr.Length > 0)
                                {
                                    smaitong.ProductImgMain = picArr[0];//主图
                                    for (int j = 0; j < picArr.Length; j++)
                                    {
                                        //最多添加8张图片
                                        if (j < 9)
                                        {
                                            productImg += picArr[j] + ";";
                                        }
                                    }
                                    productImg = productImg.Substring(0, productImg.Length - 1);
                                    smaitong.ProductImg = productImg;
                                }
                            }
                            if (listGuiGe.Count > 0) {
                                for (int t = 0; t < listGuiGe.Count; t++)
                                {
                                    //规格1
                                    if (t == 0)
                                    {
                                        string[] zhi = listGuiGe[t].sku.Split(':');
                                        smaitong.SpecificationName1 = zhi[0];
                                        smaitong.Specification1 = zhi[1];
                                    }
                                    else if (t == 1)
                                    {
                                        string[] zhi = listGuiGe[t].sku.Split(':');
                                        smaitong.SpecificationName2 = zhi[0];
                                        smaitong.Specification2 = zhi[1];
                                    }
                                    else if (t == 2)
                                    {
                                        string[] zhi = listGuiGe[t].sku.Split(':');
                                        smaitong.SpecificationName3 = zhi[0];
                                        smaitong.Specification3 = zhi[1];
                                    }

                                    else if (t == 3)
                                    {
                                        string[] zhi = listGuiGe[t].sku.Split(':');
                                        smaitong.SpecificationName4 = zhi[0];
                                        smaitong.Specification4 = zhi[1];
                                    }

                                    else if (t == 4)
                                    {
                                        string[] zhi = listGuiGe[t].sku.Split(':');
                                        smaitong.SpecificationName5 = zhi[0];
                                        smaitong.Specification5 = zhi[1];
                                    }

                                    else if (t == 5)
                                    {
                                        string[] zhi = listGuiGe[t].sku.Split(':');
                                        smaitong.SpecificationName6 = zhi[0];
                                        smaitong.Specification6 = zhi[1];
                                    }
                                    else
                                    {
                                        break;
                                    }
                                }
                            }
                            suMaiTongList.Add(smaitong);
                            //smaitong.ProductImgMain 
                            //smaitong.specificationsInfo = table.Rows[i][2].ToString(); //唯一ID 

                        }
					}

					string url=ExportExcel(suMaiTongList); 
					Result = new ResultModel() { Code = 1, Message = "导入成功,导入" + table.Rows.Count + "个" ,Data= url };
				}
			}
			catch (Exception ex)
			{
				LogHelper.Info("导入异常，异常信息：" + ex.Message);
				Result.SetValue(ErrorType.HasError);
				return res;
			}

			res.Data = Result;
			return res;
		}


		#region 导出EXCEL



		public string ExportExcel(List<suMaiTong> sumailist)
		{

			var res = new JsonResult();
			ResultModel Result = new ResultModel();
			HSSFWorkbook hssfworkbook = null;

			using (FileStream file = new FileStream(System.Web.HttpContext.Current.Server.MapPath("~/milesTemplate/产品上传模板.xls"), FileMode.Open, FileAccess.Read))
			{
				hssfworkbook = new HSSFWorkbook(file);
			}
			HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
			MilesMode model = new MilesMode();
			DataTable CardExportDt = CreateDataTable();
			DataTable table = ToDataTable(sumailist);
            if (table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow dr = CardExportDt.NewRow();
                    dr["Parent Unique ID"] = table.Rows[i]["ProductID"].ToString();	/// 同一个商家编号的商品，在系统里将被合到一个 SPU
                    dr["Unique ID"] = table.Rows[i]["ProductID"].ToString()+i;/// 商品唯一ID
                    dr["Category"] = table.Rows[i]["Category"].ToString();/// 类别 Toys,Baby & Kids, Home & 
                    dr["English Name"] = table.Rows[i]["ProductName"].ToString(); 		/// 英文名称
                    dr["Selling Price($)"] = table.Rows[i]["Price"].ToString(); 	///  销售价  不考虑拍卖业务，在电商平台直接销售的价格
                    dr["Starting Price($)"] = 5; 		///选填 起拍价 默认5美元
                    dr["Reserve Price($)"] = "5.00"; 	///选填 保留价，保留 2 位小数点，拍卖过程中最低销售价格
                    dr["MSRP($)"] ="99.00"; 		/// 选填。原价，展示在前端的未打折之前的美金价格，保留 2 位小数  默认给99美元
                    dr["Cost Price($)"] = "50.00"; 	/// 选填。成本价，保留 2 位小数。 
                    dr["Main Image URL"] = table.Rows[i]["ProductImgMain"].ToString(); /// 主图
                    dr["Extra Image URL"] = table.Rows[i]["ProductImg"].ToString();/// 同一个商家编码最多添加 9 张其他图片。多个图片链接请用分 号（；）分隔
                    dr["Description"] = table.Rows[i]["productInfo"].ToString();	/// 描述
                    dr["Shipping Fee($)"] = "3"; 		/// 选填。运费价格
                    dr["Shipping Weight(g)"] = null;	/// 选填。包装后的重量（g）， 填整数
             
                    dr["Specification Image URL"] = null;				/// 选填。规格值图片，不填默认用户选择后显示主图。
                    dr["Quantity"] = "9999";		/// 必填。库存数量，填数字。  9999                   
                    dr["arningQuantity"] = "7-17";        /// 必填。预计物流天数  7-17天
                    dr["Shipping Date"] = null;
                    dr["Country"] = null;
                    if (dr["SpecificationName1"] != null)
                    {
                        dr["SpecificationName1"] = table.Rows[i]["SpecificationName1"].ToString();
                        dr["Specification1"] = table.Rows[i]["Specification1"].ToString();
                    }

                    if (dr["SpecificationName2"] != null)
                    {
                        dr["SpecificationName2"] = table.Rows[i]["SpecificationName2"].ToString();
                        dr["Specification2"] = table.Rows[i]["Specification2"].ToString();
                    }

                    if (dr["SpecificationName3"] != null)
                    {
                        dr["SpecificationName3"] = table.Rows[i]["SpecificationName3"].ToString();
                        dr["Specification3"] = table.Rows[i]["Specification3"].ToString();
                    }

                    if (dr["SpecificationName4"] != null)
                    {
                        dr["SpecificationName4"] = table.Rows[i]["SpecificationName4"].ToString();
                        dr["Specification4"] = table.Rows[i]["Specification4"].ToString();
                    }

                    if (dr["SpecificationName5"] != null)
                    {
                        dr["SpecificationName5"] = table.Rows[i]["SpecificationName5"].ToString();
                        dr["Specification5"] = table.Rows[i]["Specification5"].ToString();
                    }

                    if (dr["SpecificationName6"] != null)
                    {
                        dr["SpecificationName6"] = table.Rows[i]["SpecificationName6"].ToString();
                        dr["Specification6"] = table.Rows[i]["Specification6"].ToString();
                    }
                    CardExportDt.Rows.Add(dr);

                    int a = 1;
                    int startRow = 1; //循环放入数据-数据起始行
                    DataRow tempDataRow = CardExportDt.Rows[0];
                    HSSFRow tempRow = (HSSFRow)sheet.GetRow(startRow);
                    this.SetCardHssfRowValue(tempRow, tempDataRow);


                    HSSFRow row = (HSSFRow)sheet.CreateRow(startRow + a);
                    row.HeightInPoints = tempRow.HeightInPoints;
                    row.Height = tempRow.Height;
                    //创建列
                    for (int c = 0; c < tempRow.Cells.Count; c++)
                    {
                        ICell cell = row.CreateCell(c);
                        ICell sourceCell = tempRow.GetCell(c);
                        cell.CellStyle = sourceCell.CellStyle;
                        cell.SetCellType(sourceCell.CellType);
                    }
                //    this.SetCardHssfRowValue(row, CardExportDt.Rows[i]);
                }
            } 
			    NPOIEXcelHelper.DataTableToExcel(CardExportDt, Server.MapPath("~/write/aa" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"));

            return System.Web.HttpContext.Current.Server.MapPath("~/write/aa" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
                //NPOIEXcelHelper.NPOIExport(string.Format("{0}{1}", "", DateTime.Now.ToString("yyyyMMdd")) + ".xls", hssfworkbook, new List<HSSFSheet>() { sheet });


        }
		int cardStartIndex = 1;
		/// <summary>
		/// 
		/// 设置某一行的所有Cell值
		/// </summary>
		/// <param name="row"></param>
		/// <param name="dataRow"></param>
		/// <param name="index"></param>
		private void SetCardHssfRowValue(HSSFRow row, DataRow dataRow)
		{
			row.Cells[0].SetCellValue(cardStartIndex - 1);
			row.Cells[1].SetCellValue(dataRow["Parent Unique ID"].ToString());
			row.Cells[2].SetCellValue(dataRow["Unique ID"].ToString());
            row.Cells[3].SetCellValue(dataRow["Category"].ToString());
            row.Cells[4].SetCellValue(dataRow["English Name"].ToString());
            row.Cells[5].SetCellValue(dataRow["Selling Price($)"].ToString());
            row.Cells[6].SetCellValue(dataRow["Starting Price($)"].ToString());
            row.Cells[7].SetCellValue(dataRow["Reserve Price($)"].ToString());
            row.Cells[8].SetCellValue(dataRow["MSRP($)"].ToString());
            row.Cells[9].SetCellValue(dataRow["Cost Price($)"].ToString());
            row.Cells[10].SetCellValue(dataRow["Main Image URL"].ToString());
            row.Cells[11].SetCellValue(dataRow["Extra Image URL"].ToString());
            row.Cells[12].SetCellValue(dataRow["Description"].ToString());
            row.Cells[13].SetCellValue(dataRow["Shipping Fee($)"].ToString());
            row.Cells[14].SetCellValue(dataRow["Shipping Weight(g)"].ToString());
 
            row.Cells[15].SetCellValue(dataRow["Specification Image URL"].ToString());
            row.Cells[16].SetCellValue(dataRow["Quantity"].ToString());
            row.Cells[17].SetCellValue(dataRow["arningQuantity"].ToString());
            row.Cells[18].SetCellValue(dataRow["Shipping Date"].ToString());
            //    row.Cells[19].SetCellValue(dataRow["ShippingCountry"].ToString());
            /*  if (!string.IsNullOrWhiteSpace(dataRow["SpecificationName1"].ToString()))
              {
                  row.Cells[19].SetCellValue(dataRow["SpecificationName1"].ToString()); 
                  row.Cells[20].SetCellValue(dataRow["Specification1"].ToString());
              }

              if (!string.IsNullOrWhiteSpace(dataRow["SpecificationName2"].ToString()))
              {
                  row.Cells[21].SetCellValue(dataRow["SpecificationName2"].ToString());
                  row.Cells[22].SetCellValue(dataRow["Specification2"].ToString());
              }

              if (!string.IsNullOrWhiteSpace(dataRow["SpecificationName3"].ToString()))
              {
                  row.Cells[23].SetCellValue(dataRow["SpecificationName3"].ToString());
                  row.Cells[24].SetCellValue(dataRow["Specification3"].ToString());
              }

              if (!string.IsNullOrWhiteSpace(dataRow["SpecificationName4"].ToString()))
              {
                  row.Cells[26].SetCellValue(dataRow["SpecificationName4"].ToString());
                  row.Cells[27].SetCellValue(dataRow["Specification4"].ToString());
              }

              if (!string.IsNullOrWhiteSpace(dataRow["SpecificationName5"].ToString()))
              {
                  row.Cells[28].SetCellValue(dataRow["SpecificationName5"].ToString());
                  row.Cells[29].SetCellValue(dataRow["Specification5"].ToString());
              }

              if (!string.IsNullOrWhiteSpace(dataRow["SpecificationName6"].ToString()))
              {
                  row.Cells[30].SetCellValue(dataRow["SpecificationName6"].ToString());
                  row.Cells[31].SetCellValue(dataRow["Specification6"].ToString());
              } */
            //row.Cells[22].SetCellValue(dataRow["CardPwd"].ToString()); 
            cardStartIndex++;
		}

		private void SetCardHssfRowValueExcel(HSSFRow row, DataRow dataRow)
		{
			row.Cells[0].SetCellValue(cardStartIndex);
			row.Cells[1].SetCellValue(dataRow["CardNo"].ToString());
			cardStartIndex++;
		}
		public DataTable CreateDataTable()
		{
			DataTable dt = new DataTable("CardExportDT"); 
			DataColumn dc1 = new DataColumn("Parent Unique ID", Type.GetType("System.String"));
			DataColumn dc2 = new DataColumn("Unique ID", Type.GetType("System.String"));
			DataColumn dc3 = new DataColumn("Category", Type.GetType("System.String"));
			DataColumn dc4 = new DataColumn("English Name", Type.GetType("System.String"));
			DataColumn dc5 = new DataColumn("Selling Price($)", Type.GetType("System.String"));
			DataColumn dc6 = new DataColumn("Starting Price($)", Type.GetType("System.String"));
			DataColumn dc7 = new DataColumn("Reserve Price($)", Type.GetType("System.String"));
			DataColumn dc8 = new DataColumn("MSRP($)", Type.GetType("System.String"));
			DataColumn dc9 = new DataColumn("Cost Price($)", Type.GetType("System.String"));
			DataColumn dc10 = new DataColumn("Main Image URL", Type.GetType("System.String"));
			DataColumn dc11 = new DataColumn("Extra Image URL", Type.GetType("System.String"));
			DataColumn dc12 = new DataColumn("Description", Type.GetType("System.String"));
			DataColumn dc13 = new DataColumn("Shipping Fee($)", Type.GetType("System.String"));
			DataColumn dc14 = new DataColumn("Shipping Weight(g)", Type.GetType("System.String"));
			DataColumn dc15 = new DataColumn("Color", Type.GetType("System.String"));
			DataColumn dc16 = new DataColumn("Size", Type.GetType("System.String"));
			DataColumn dc17= new DataColumn("Specification Image URL", Type.GetType("System.String"));
			DataColumn dc18 = new DataColumn("Quantity", Type.GetType("System.String"));
			DataColumn dc19 = new DataColumn("Warning Quantity", Type.GetType("System.String"));
			DataColumn dc20 = new DataColumn("Shipping Date", Type.GetType("System.String"));
			DataColumn dc21 = new DataColumn("Shipping Country", Type.GetType("System.String"));
			DataColumn dc22 = new DataColumn("Country", Type.GetType("System.String")); 
            DataColumn dc23 = new DataColumn("SpecificationName1", Type.GetType("System.String"));
            DataColumn dc24 = new DataColumn("Specification1", Type.GetType("System.String"));
             
            DataColumn dc25 = new DataColumn("SpecificationName2", Type.GetType("System.String"));
            DataColumn dc26 = new DataColumn("Specification2", Type.GetType("System.String")); 

            DataColumn dc27 = new DataColumn("SpecificationName3", Type.GetType("System.String"));
            DataColumn dc28 = new DataColumn("Specification3", Type.GetType("System.String")); 

            DataColumn dc29 = new DataColumn("SpecificationName4", Type.GetType("System.String"));
            DataColumn dc30 = new DataColumn("Specification4", Type.GetType("System.String")); 

            DataColumn dc31 = new DataColumn("SpecificationName5", Type.GetType("System.String"));
            DataColumn dc32 = new DataColumn("Specification5", Type.GetType("System.String")); 

            DataColumn dc33 = new DataColumn("SpecificationName6", Type.GetType("System.String"));
            DataColumn dc34 = new DataColumn("Specification6", Type.GetType("System.String")); 
DataColumn dc35 = new DataColumn("arningQuantity", Type.GetType("System.String")); 

            dt.Columns.Add(dc1);
			dt.Columns.Add(dc2);
			dt.Columns.Add(dc3);
			dt.Columns.Add(dc4);
			dt.Columns.Add(dc5);
			dt.Columns.Add(dc6);
			dt.Columns.Add(dc7);
			dt.Columns.Add(dc8);
			dt.Columns.Add(dc9);
			dt.Columns.Add(dc10);
			dt.Columns.Add(dc11);
			dt.Columns.Add(dc12);
			dt.Columns.Add(dc13);
			dt.Columns.Add(dc14);
			dt.Columns.Add(dc15);
			dt.Columns.Add(dc16);
			dt.Columns.Add(dc17);
			dt.Columns.Add(dc18);
			dt.Columns.Add(dc19);
            dt.Columns.Add(dc20);
            dt.Columns.Add(dc21);
            dt.Columns.Add(dc22);
            dt.Columns.Add(dc23);
            dt.Columns.Add(dc24);
            dt.Columns.Add(dc25);
            dt.Columns.Add(dc26);
            dt.Columns.Add(dc27);
            dt.Columns.Add(dc28);
            dt.Columns.Add(dc29);
            dt.Columns.Add(dc30);
            dt.Columns.Add(dc31);
            dt.Columns.Add(dc32);
            dt.Columns.Add(dc33);
            dt.Columns.Add(dc34); 
            dt.Columns.Add(dc35);


            return dt;
		}


		/// <summary>
		/// Convert a List{T} to a DataTable.
		/// </summary>
		private DataTable ToDataTable<T>(List<T> items)
		{
			var tb = new DataTable(typeof(T).Name);

			PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

			foreach (PropertyInfo prop in props)
			{
				Type t = GetCoreType(prop.PropertyType);
				tb.Columns.Add(prop.Name, t);
			}

			foreach (T item in items)
			{
				var values = new object[props.Length];

				for (int i = 0; i < props.Length; i++)
				{
					values[i] = props[i].GetValue(item, null);
				}

				tb.Rows.Add(values);
			}

			return tb;
		}

		/// <summary>
		/// Determine of specified type is nullable
		/// </summary>
		public static bool IsNullable(Type t)
		{
			return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
		}

		/// <summary>
		/// Return underlying type if type is Nullable otherwise return the type
		/// </summary>
		public static Type GetCoreType(Type t)
		{
			if (t != null && IsNullable(t))
			{
				if (!t.IsValueType)
				{
					return t;
				}
				else
				{
					return Nullable.GetUnderlyingType(t);
				}
			}
			else
			{
				return t;
			}
		}
		#endregion
	}
}